package com.iot08.api.utils;

import com.iot08.common.utils.DateUtil;
import com.iot08.common.utils.StringUtil;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.HashSet;
import java.util.Set;

/**
 * @version 1.0
 * @Description: Excel工具
 * @time: 2020/8/27 16:01
 * @author: Rock
 */
public class ExcelUtil {

    /**
     * 获取Excel某个单元格的值
     *
     * @param sheet
     * @param x
     * @param y
     * @return Object
     */
    public static Object getCellValue(Sheet sheet, int x, int y) {
        Object rtn = null;
        Row row = sheet.getRow(y);
        if (row != null) {
            Cell cell = row.getCell(x);
            if (cell != null) {
                rtn = getValue(cell);
            }
        }
        return rtn;
    }

    /**
     * @param cell
     * @return
     * @author:rock
     * @time:2020年4月22日 下午6:29:18
     */
    public static Object getValue(Cell cell) {
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    return cell.getStringCellValue() == null ? null : cell.getStringCellValue().trim();
                case Cell.CELL_TYPE_NUMERIC:
                    HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
                    return dataFormatter.formatCellValue(cell);
                case Cell.CELL_TYPE_BOOLEAN:
                    return cell.getBooleanCellValue();
                case Cell.CELL_TYPE_ERROR:
                    return cell.getErrorCellValue();
                default:
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    return cell.getStringCellValue() == null ? null : cell.getStringCellValue().trim();
            }
        }
        return null;
    }

    /**
     * @param [cell, value]
     * @time: 2020/8/31 15:35
     * @author: Rock
     * @version 1.0
     */
    public static void setCellValue(Cell cell, Object value) {
        if (value == null) {
            return;
        } else if (value instanceof String) {
            cell.setCellValue(value.toString());
        } else if (value instanceof Integer) {
            cell.setCellValue(((Integer) value).intValue());
        } else if (value instanceof Long) {
            cell.setCellValue(((Long) value).longValue());
        } else if (value instanceof Float) {
            cell.setCellValue(((Float) value).floatValue());
        } else if (value instanceof Double) {
            cell.setCellValue(((Double) value).doubleValue());
        } else if (value instanceof Boolean) {
            cell.setCellValue(((Boolean) value).booleanValue());
        } else if (value instanceof BigInteger) {
            cell.setCellValue(((BigInteger) value).longValue());
        } else if (value instanceof BigDecimal) {
            cell.setCellValue(((BigDecimal) value).doubleValue());
        } else if (value instanceof Date) {
            cell.setCellValue(DateUtil.dateToStr((Date) value, "yyyy-MM-dd"));
        } else if (value instanceof Timestamp) {
            cell.setCellValue(DateUtil.dateToStr(new Date(((Timestamp) value).getTime()), "yyyy-MM-dd HH:mm:ss"));
        } else if (value instanceof Time) {
            cell.setCellValue((DateUtil.dateToStr(new Date(((Time) value).getTime()), "yyyy-MM-dd HH:mm:ss")));
        } else {
            cell.setCellValue(value.toString());
        }
    }

    /**
     * @param sheet
     * @time: 2020/12/11 9:03
     * @author: Rock
     * @version 1.0
     */
    public static Set<String> getImei(Sheet sheet) {
        Set<String> set = new HashSet<>();
        for (int i = 0; i <= sheet.getPhysicalNumberOfRows(); i++) {
            Object v = ExcelUtil.getCellValue(sheet, 0, i);
            if (StringUtil.isBlank(v)) {
                continue;
            }
            set.add(v.toString().trim());
        }
        return set;
    }

}
